package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

/**
 * Performs folder dao operations
 * @author Sukhjinder
 */
class FolderSqlDao implements FolderDao {
	
	// Connection
	private Connection conn;
	
	// Statements
    private final String INSERT = "INSERT INTO folders VALUES(?, ?);";
    private final String SELECT = "SELECT Name, UserId FROM folders WHERE Id = ?;";
    private final String SELECTBYNAMEANDUSER = "SELECT Id FROM folders WHERE Name = ? AND UserId = ?;";
    private final String SELECTUSERFOLDER = "SELECT * FROM folders WHERE UserId = ?;";
    private final String UPDATE = "UPDATE folders " +
    		"SET Name = ? " +
    		"WHERE Id = ?;";
    private final String DELETE = "DELETE FROM folders WHERE Id = ?;";
    
    /**
     * Constructor
     * @param conn connection object for database
     */
    public FolderSqlDao(Connection conn) {
		this.conn = conn;
	}

	@Override
    public int insert(Map<String, String> properties) throws Exception {
        PreparedStatement statement = conn.prepareStatement(INSERT); 
        conn.setAutoCommit(true);
        int folderId=-1;
        
        statement.setString(1, properties.get("name"));
        statement.setString(2, properties.get("userId"));        
        statement.executeUpdate();
        
        ResultSet rs = statement.getGeneratedKeys();
        if (rs.next()){
        	folderId=rs.getInt(1);
        }
        rs.close();
        
        return folderId;
                
    }

	@Override
    public Map<String, String> select(int id) throws Exception {
        Map<String, String> properties = new HashMap<>();

        PreparedStatement statement = conn.prepareStatement(SELECT);
        statement.setString(1, String.valueOf(id));
        ResultSet rs = statement.executeQuery();
        rs.next();
        properties.put("name", rs.getString(1));
        properties.put("userId", rs.getString(2));
        rs.close();
            
        return properties;
    }

	@Override
	public Map<String, String> selectByNameAndUser(String name, int userId)
			throws Exception {
		
		Map<String, String> properties = new HashMap<>();

        PreparedStatement statement = conn.prepareStatement(SELECTBYNAMEANDUSER);
        statement.setString(1, name);
        statement.setString(2, String.valueOf(userId));
        ResultSet rs = statement.executeQuery();
        if (rs.next() == false) {
        	properties.put("error", "Record not found");
        } else {
        	properties.put("id", rs.getString(1));
        }
        rs.close();
            
        return properties;
	}
	
	@Override
	public Map<Integer, String[]> selectUserFolders(int userId)
			throws Exception {
		
		Map<Integer, String[]> properties = new HashMap<>();
		String folderProperties[] = new String[2];
		int index = 0, key;
        PreparedStatement statement = conn.prepareStatement(SELECTUSERFOLDER);
        statement.setString(1, String.valueOf(userId));
        ResultSet rs = statement.executeQuery();
        if (rs.next() == false) {
        	folderProperties[index] = new String("No Records found");
        	properties.put(index, folderProperties);
        } else {
        	do
            {
        		index = 0;
        		key = rs.getInt(1);
        		folderProperties[index++] = rs.getString(2);
        		folderProperties[index++] = rs.getString(3);
        		properties.put(key, folderProperties);
        	} while (rs.next());                    
        }
        rs.close();            
        return properties;
	}

	@Override
    public void update(Map<String, String> properties) throws Exception {
        PreparedStatement statement = conn.prepareStatement(UPDATE);
        conn.setAutoCommit(true);
        statement.setString(1, properties.get("name"));
        statement.setString(2, properties.get("id"));
        statement.executeUpdate();
    }

	@Override
	public void delete(int id) throws Exception {
		PreparedStatement statement = conn.prepareStatement(DELETE);
        conn.setAutoCommit(true);
        statement.setString(1, String.valueOf(id));
        statement.executeUpdate();
	}

}
